drop PROCEDURE [dbo].[sp_GetEntities]
GO
CREATE PROCEDURE [dbo].[sp_GetEntities]
	@TableName NVARCHAR(128),
	@ReturnFields NVARCHAR(2000),
	@TopCount int,
    @SqlFullPopulate NVARCHAR(4000),
    @SqlPopulate NVARCHAR(4000),
	@VariableDecalrations NVARCHAR(1024),
    @ParameterString1 NVARCHAR(512),
    @ParameterString2 NVARCHAR(512),
    @ParameterString3 NVARCHAR(512),
    @ParameterString4 NVARCHAR(512),
	@DisOrderSql NVARCHAR(256),
	@TotalRecords int OUTPUT
AS

DECLARE @totalSQL NVARCHAR(4000)
DECLARE @sqlBegin NVARCHAR(1024)
DECLARE @sqlPart1 NVARCHAR(4000)
DECLARE @sqlPart2 NVARCHAR(4000)
DECLARE @sqlPart3 NVARCHAR(4000)
DECLARE @sqlEnd NVARCHAR(1024)

-- Set the begin SQL
set @sqlBegin = N'
SET NOCOUNT ON

CREATE TABLE #t
(
    IndexId INT IDENTITY (1, 1) NOT NULL,
    EntityId INT
)
CREATE TABLE #EntityIdTable
(
    EntityId INT
)

DECLARE @TotalRecords int
set @TotalRecords = 0

'

-- Set the end SQL
set @sqlEnd = N'
DROP TABLE #t
DROP TABLE #EntityIdTable

set @totalCount = @TotalRecords

SET NOCOUNT OFF

'

-- Set the SQL to get the total records count.
set @sqlPart1 = N'EXECUTE sp_executesql N''SELECT @TotalRecords = COUNT(*) FROM (' + @SqlFullPopulate + N') a''' + ', N''' + @ParameterString1 + N'@TotalRecords INT OUTPUT'', ' + @ParameterString2 + N'@TotalRecords OUTPUT'

-- Set the SQL to get the record keys of the current page.
set @sqlPart2 = N'EXECUTE sp_executesql N''' + N'INSERT INTO #t(EntityId) SELECT TOP ' + CAST(@TopCount AS NVARCHAR(20)) + N' EntityId FROM (' + @SqlPopulate + N') t ORDER BY ' + @DisOrderSql + N'''' + @ParameterString3 + @ParameterString4 + N'; INSERT INTO #EntityIdTable SELECT EntityId FROM #t ORDER BY IndexId DESC'

-- Set the SQL to get the record entities of the current page.
set @sqlPart3 = N'EXECUTE sp_executesql N''SELECT ' + @ReturnFields + N' FROM #EntityIdTable dt INNER JOIN [' + @TableName + N'] t ON dt.EntityId = t.EntityId'''

-- Set the total whole SQL
set @totalSQL = @sqlBegin + @VariableDecalrations
+ N'
'
+ @sqlPart1
+ N'

'
+ @sqlPart2
+ N'

'
+ @sqlPart3
+ N'

'
+ @sqlEnd

-- Execute the total whole SQL
EXECUTE sp_executesql @totalSQL, N'@totalCount INT OUTPUT', @TotalRecords OUTPUT
print @totalSQL

GO